/**
 * 
 */
package myproject.main;

import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.alibaba.fastjson.JSON;

import myproject.api.ThreeD;
import myproject.mapper.ArrayThreeMapper;
import myproject.mapper.ThreeDataMapper;
import myproject.utils.SqlSessionFactoryUtils;

/**@version:
 * @Description: 数据库操作
 * @author ：luokai
 * @date: date{2021年1月6日} 
 */
public class ArrayDatabase {
	private static final Logger log = Logger.getLogger(ArrayDatabase.class);
	/**
	 *读取TXT文件信息，计算和值
	 * @param filePath
	 * @return
	 */
	
	public static List<ThreeD> readTxtFile(String  filePath ) {		
		
		if(filePath==null||"".equals(filePath)) {
			log.info("filePath:为空------------------------");
			return null;
		}
		
		List<ThreeD> threedlist=new ArrayList<ThreeD> ();
		
		File file = new File(filePath);
		try {
		String encoding = "UTF-8";
		if (file.isFile() && file.exists()) {
			long startTime=System.currentTimeMillis();
			InputStreamReader read = new InputStreamReader(new FileInputStream(file), encoding);

			BufferedReader bufferedReader = new BufferedReader(read);

			String lineTxt = null;
			
			
			while ((lineTxt = bufferedReader.readLine()) != null) {
				//将一行数据按空格截取
				String[] s = lineTxt.split("\\s+");
				String result=s[2]+s[3]+s[4];
				
				ThreeD arg0=new ThreeD();
				arg0.setDate(s[0]);
				arg0.setIssue(s[1]);
				arg0.setResult(result);
				arg0.setHundreds(Integer.parseInt(s[2]));
				arg0.setTens(Integer.parseInt(s[3]));
				arg0.setOnes(Integer.parseInt(s[4]));
				arg0.setSum(Integer.parseInt(s[2])+Integer.parseInt(s[3])+Integer.parseInt(s[4]));
				
				threedlist.add(arg0);
			}
			long endTime=System.currentTimeMillis();
	        log.info("读取方法执行时间："+(endTime-startTime)+"ms");
			
	        read.close();
	        log.info("threedlist size:"+threedlist.size());
	        return threedlist;
		} else {
			log.info("找不到指定的文件");
			return null;
		}
		
		
		} catch (Exception e) {
			log.info("读取文件时发生异常！", e);
		}

		return null;
	}	
	

public static void insertThreeData(List<ThreeD> threelist ) {
		SqlSession sqlSession = null;
		try {
			sqlSession = SqlSessionFactoryUtils.openSqlSession();
			ThreeDataMapper threeDataMapper = sqlSession.getMapper(ThreeDataMapper.class);

			
			if(threelist.size()>0) {
				for(int i=0;i<threelist.size();i++) {
					if(threelist.get(i).getIssue().length()==7) {
						
					threeDataMapper.insertThreeD(threelist.get(i));
					
					}else {
						log.info("期号位数发生异常！");
						throw new RuntimeException("期号位数发生异常！"); 
					}
				}
				
			}
			
			sqlSession.commit();//提交数据库操作
		}  catch(Exception e) {
		    e.printStackTrace();
		}finally {
			if (sqlSession != null) {
				sqlSession.close();
			}
		}
	}

public static void  insertArrayThree(List<ThreeD> threelist ) {
	SqlSession sqlSession = null;
	try {
		sqlSession = SqlSessionFactoryUtils.openSqlSession();
		ArrayThreeMapper arrayThreeMapper = sqlSession.getMapper(ArrayThreeMapper.class);

		
		if(threelist.size()>0) {
			for(int i=0;i<threelist.size();i++) {
				if(threelist.get(i).getIssue().length()==5) {
					
					arrayThreeMapper.insertThreeD(threelist.get(i));
					
					}else {
						log.info("期号位数发生异常！");
						throw new RuntimeException("期号位数发生异常！"); 
					}
				
			}
		}
		
		sqlSession.commit();//提交数据库操作
	} catch(Exception e) {
	    e.printStackTrace();
	}
	finally {
		if (sqlSession != null) {
			sqlSession.close();
		}
	}
}



	
/**
 * 更新已有数据的跨度，形态，组选，除三余数，组三中的重复数字
*/
public static void updateThreeData() {
			SqlSession sqlSession = null;
			try {
				sqlSession = SqlSessionFactoryUtils.openSqlSession();
				ThreeDataMapper threeDataMapper = sqlSession.getMapper(ThreeDataMapper.class);
				ArrayCount arrayCount=new ArrayCount();
				List<ThreeD> threelist;
				threelist=threeDataMapper.queryList();
				log.info("threelist:"+threelist.size());
				if(threelist.size()>0) {
					for(int i=0;i<threelist.size();i++) {
						//减少重复计算，对于已经更新过的数据不再处理
						if(threelist.get(i).getForm() == null || threelist.get(i).getForm().length() <= 0 ) {
							threelist.get(i).setSpan(arrayCount.countSpan(threelist.get(i))); 
							threelist.get(i).setForm(arrayCount.countForm(threelist.get(i)));
							threelist.get(i).setPacket(arrayCount.countPacket(threelist.get(i)));
							threelist.get(i).setRemainder(arrayCount.countRemainder(threelist.get(i)));
							
							threeDataMapper.updateThreeD(threelist.get(i));
							
							
						}
						}
					
					//直接用sql实现更新组三中的重复数字
					threeDataMapper.updateArray();
				}
				sqlSession.commit();//提交数据库操作

				
			} finally {
				if (sqlSession != null) {
					sqlSession.close();
				}
			}
		}	



/**更新已有数据的跨度，形态，组选，除三余数，组三中的重复数字
*/
public static void updateArrayThree() {
			SqlSession sqlSession = null;
			try {
				sqlSession = SqlSessionFactoryUtils.openSqlSession();
				ArrayThreeMapper arrayThreeMapper = sqlSession.getMapper(ArrayThreeMapper.class);
				ArrayCount arrayCount=new ArrayCount();
				List<ThreeD> threelist;
				threelist=arrayThreeMapper.queryList();
				log.info("threelist:"+threelist.size());
				if(threelist.size()>0) {
					for(int i=0;i<threelist.size();i++) {
						//减少重复计算，对于已经更新过的数据不再处理
						if(threelist.get(i).getForm() == null || threelist.get(i).getForm().length() <= 0 ) {
							threelist.get(i).setSpan(arrayCount.countSpan(threelist.get(i))); 
							threelist.get(i).setForm(arrayCount.countForm(threelist.get(i)));
							threelist.get(i).setPacket(arrayCount.countPacket(threelist.get(i)));
							threelist.get(i).setRemainder(arrayCount.countRemainder(threelist.get(i)));
							
							arrayThreeMapper.updateThreeD(threelist.get(i));
						}
						}
					//直接用sql实现更新组三中的重复数字
					arrayThreeMapper.updateArray();
				}
				sqlSession.commit();//提交数据库操作

				
			} finally {
				if (sqlSession != null) {
					sqlSession.close();
				}
			}
		}	

		/**
		 **  按照和值大小分类txt
		 **
		 **/
		public static void assortTxt(String path,String lotteryType){
			long startTime=System.currentTimeMillis();
			Date date = new Date();
			SimpleDateFormat dateFormat= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String number="16";
			
			SqlSession sqlSession = null;
			BufferedWriter bw = null;
			try {
				
				sqlSession = SqlSessionFactoryUtils.openSqlSession();
				ThreeDataMapper threeDataMapper = sqlSession.getMapper(ThreeDataMapper.class);
				ArrayThreeMapper arrayThreeMapper = sqlSession.getMapper(ArrayThreeMapper.class);
				List<ThreeD> threelist;
				String letter="W_";
				for(int i=0;i<=27;i++) {
					number=Integer.toString(i);
					if(lotteryType.equals("welfare")) {
						
						threelist=threeDataMapper.querySum(number);
					}else {
						letter="S_";
						threelist=arrayThreeMapper.querySum(number);
					}
					
					File file = new File(path+letter+number+".txt");//文件路径（路径+文件名）
					if (!file.exists()) {   //文件不存在则创建文件，先创建目录
						File dir = new File(file.getParent());
						dir.mkdirs();
						file.createNewFile();
					}
					FileWriter fileWriter = new FileWriter(file); //文件输出流将数据写入文件
					
					bw = new BufferedWriter(fileWriter);
							
					bw.write(dateFormat.format(date)+" 和值"+number+"数据");
					bw.write("\r\n"); 
					bw.write(" 期号		日期	 结果  百  十  个  形态	 跨度  组选	 除三	遗漏");
					bw.write("\r\n"); 
					for(int j=0;j<threelist.size();j++) {
						String data=threelist.get(j).getIssue()+"   "+threelist.get(j).getDate()+"   "+threelist.get(j).getResult()+"   "
								+Integer.toString(threelist.get(j).getHundreds())+"   "+Integer.toString(threelist.get(j).getTens())+"   "
								+Integer.toString(threelist.get(j).getOnes())+"   "+threelist.get(j).getForm()+"   "+threelist.get(j).getSpan() 
								+"    "+threelist.get(j).getPacket()+"    "+threelist.get(j).getRemainder()+"    "+threelist.get(j).getDaysofdifference();
						bw.write(data);
						bw.write("\r\n"); 
					}
					bw.close();
					log.info("分类完毕！"+number+".txt");
				}
				
			} catch (Exception e) {
				e.printStackTrace();
				// do something
			} finally {
				// do something
				if (sqlSession != null) {
					sqlSession.close();
				}
				long endTime=System.currentTimeMillis();
				log.info("txt分类方法执行时间："+(endTime-startTime)+"ms");
				log.info("txt分类方法执行时间："+(endTime-startTime)+"ms");
				log.info("txt分类方法执行时间："+(endTime-startTime)+"ms");
			}
			
			
		}
		
		
		/**
		 **  按照和值大小分类txt
		 **
		 **/
		public static void assortNewTxt(String path,String lotteryType){
			long startTime=System.currentTimeMillis();
			Date date = new Date();
			SimpleDateFormat dateFormat= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String number="16";
			
			SqlSession sqlSession = null;
			BufferedWriter bw = null;
			try {
				
				sqlSession = SqlSessionFactoryUtils.openSqlSession();
				ThreeDataMapper threeDataMapper = sqlSession.getMapper(ThreeDataMapper.class);
				ArrayThreeMapper arrayThreeMapper = sqlSession.getMapper(ArrayThreeMapper.class);
				List<ThreeD> threelist;
				String letter="W_";
				for(int i=0;i<=27;i++) {
					number=Integer.toString(i);
					if(lotteryType.equals("welfare")) {
						
						threelist=threeDataMapper.queryNewSum(number);
					}else {
						letter="S_";
						threelist=arrayThreeMapper.queryNewSum(number);
					}
					
					File file = new File(path+letter+number+".txt");//文件路径（路径+文件名）
					if (!file.exists()) {   //文件不存在则创建文件，先创建目录
						File dir = new File(file.getParent());
						dir.mkdirs();
						file.createNewFile();
					}
					FileWriter fileWriter = new FileWriter(file); //文件输出流将数据写入文件
					
					bw = new BufferedWriter(fileWriter);
							
					bw.write(dateFormat.format(date)+" 和值"+number+"数据");
					bw.write("\r\n"); 
					bw.write(" 期号		日期	 结果  百  十  个    形态 跨度   组选	除三 遗漏    	大小	   奇偶	    质合	 升降 区间"
							);
					bw.write("\r\n"); 
					for(int j=0;j<threelist.size();j++) {
						String data=threelist.get(j).getIssue()+"   "+threelist.get(j).getDate()+"   "+threelist.get(j).getResult()+"   "
								+Integer.toString(threelist.get(j).getHundreds())+"   "+Integer.toString(threelist.get(j).getTens())+"   "
								+Integer.toString(threelist.get(j).getOnes())+"   "+threelist.get(j).getForm()+"   "+threelist.get(j).getSpan()+"    " 
								+threelist.get(j).getPacket()+"    "+threelist.get(j).getRemainder()+"    "+threelist.get(j).getDaysofdifference()+"\t"+"    "
								+threelist.get(j).getSize()+"    "+threelist.get(j).getOddeven()+"    "+threelist.get(j).getPrimeOrComposite()+"    "
								+threelist.get(j).getOrder()+"    "+threelist.get(j).getInterval();
						bw.write(data);
						bw.write("\r\n"); 
					}
					bw.close();
					log.info("分类完毕！"+number+".txt");
				}
				
			} catch (Exception e) {
				e.printStackTrace();
				// do something
			} finally {
				// do something
				if (sqlSession != null) {
					sqlSession.close();
				}
				long endTime=System.currentTimeMillis();
				log.info("txt分类方法执行时间："+(endTime-startTime)+"ms");
				log.info("txt分类方法执行时间："+(endTime-startTime)+"ms");
				log.info("txt分类方法执行时间："+(endTime-startTime)+"ms");
			}
			
			
		}
		
		/**
		 **  按照和值大小分类xls
		 **
		 **/
		public static void assortXls(String path,String lotteryType){
			
			long startTime=System.currentTimeMillis();
			Date date = new Date();
			SimpleDateFormat dateFormat= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			log.info(dateFormat.format(date));
			//创建HSSFWorkbook对象  
		   	HSSFWorkbook wb = new HSSFWorkbook(); 
		   	SXSSFWorkbook sxssbook=new SXSSFWorkbook();
			
			 String number="23";
			 String fileURL="";
			 SqlSession sqlSession = null;
			 BufferedOutputStream bos = null;
			 try {
					sqlSession = SqlSessionFactoryUtils.openSqlSession();
					ThreeDataMapper threeDataMapper = sqlSession.getMapper(ThreeDataMapper.class);
					ArrayThreeMapper arrayThreeMapper = sqlSession.getMapper(ArrayThreeMapper.class);
					//按和值分类开奖数据
					List<ThreeD> threelist;
					//按和值统计信息数据
					List<Map<Integer,Integer>> statisticsList;
				
					
					for(int i=0;i<=27;i++) {
						    number=Integer.toString(i);
							if(lotteryType.equals("welfare")) {
								threelist=threeDataMapper.querySum(number);
								statisticsList =threeDataMapper.statisticsBySum(number);
								fileURL=path+"ThreeData.xls";
							}else {
								threelist=arrayThreeMapper.querySum(number);
								statisticsList =arrayThreeMapper.statisticsBySum(number);
								fileURL=path+"ArrayThree.xls";
							}
							
							//创建HSSFSheet对象  
							HSSFSheet sheet = wb.createSheet(number); 
							//创建行  
							HSSFRow row2=sheet.createRow(0);      
							//创建单元格并设置单元格内容  
							row2.createCell(0).setCellValue("日期");  
							row2.createCell(1).setCellValue("期号");      
							row2.createCell(2).setCellValue("开奖号码");  
							row2.createCell(3).setCellValue("百位");  
							row2.createCell(4).setCellValue("十位");  
							row2.createCell(5).setCellValue("个位");      
							row2.createCell(6).setCellValue("形态"); 
							row2.createCell(7).setCellValue("跨度");  
							row2.createCell(8).setCellValue("和值");
							row2.createCell(9).setCellValue("组选");
							row2.createCell(10).setCellValue("除三余数");
							row2.createCell(11).setCellValue("组三重复");   
							row2.createCell(12).setCellValue("遗漏期数");   
							
							row2.createCell(13).setCellValue("遗漏期数");  
							row2.createCell(14).setCellValue("汇总"); 
							row2.createCell(15).setCellValue("截止"+dateFormat.format(date)+"和值"+number+"数据"); 
							 
							
						   	CellStyle cellStyle = wb.createCellStyle();
						   	cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
						   	cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
					         
							HSSFFont font = wb.createFont();
							font.setFontName("Arial");
							font.setFontHeightInPoints((short) 12);
							cellStyle.setFont(font);
							
							
							//表头设置垂直居中、水平居中
							for(int j=0;j<15;j++) {
								 row2.getCell(j).setCellStyle(cellStyle);
						    }
							 
						    for(int row=0;row<threelist.size();row++) {
						    	 HSSFRow row3=sheet.createRow(row+1);      
						 	    //创建单元格并设置单元格内容  
						 	    row3.createCell(0).setCellValue(threelist.get(row).getDate());  
						 	    row3.createCell(1).setCellValue(threelist.get(row).getIssue());      
						 	    row3.createCell(2).setCellValue(threelist.get(row).getResult());  
						 	    row3.createCell(3).setCellValue(threelist.get(row).getHundreds());  
						 	    row3.createCell(4).setCellValue(threelist.get(row).getTens());  
						 	    row3.createCell(5).setCellValue(threelist.get(row).getOnes());      
						 	    row3.createCell(6).setCellValue(threelist.get(row).getForm()); 
						 	    row3.createCell(7).setCellValue(threelist.get(row).getSpan());
						 	    row3.createCell(8).setCellValue(threelist.get(row).getSum());
						 	    row3.createCell(9).setCellValue(threelist.get(row).getPacket());  
						 	    row3.createCell(10).setCellValue(threelist.get(row).getRemainder()); 
						 	    row3.createCell(11).setCellValue(threelist.get(row).getArray());
						 	    row3.createCell(12).setCellValue(threelist.get(row).getDaysofdifference());  
						 	    
						 	    row3.getCell(0).setCellStyle(cellStyle);
						 	    row3.getCell(1).setCellStyle(cellStyle);
						 	    row3.getCell(2).setCellStyle(cellStyle);
						 	    row3.getCell(3).setCellStyle(cellStyle);
						 	    row3.getCell(4).setCellStyle(cellStyle);
						 	    row3.getCell(5).setCellStyle(cellStyle);
						 	    row3.getCell(6).setCellStyle(cellStyle);
						 	    row3.getCell(7).setCellStyle(cellStyle);
						 	    row3.getCell(8).setCellStyle(cellStyle);
						 	    row3.getCell(9).setCellStyle(cellStyle);
						 	    row3.getCell(10).setCellStyle(cellStyle);
						 	    row3.getCell(11).setCellStyle(cellStyle);
						 	    row3.getCell(12).setCellStyle(cellStyle);
						 	    
						 	  
						    }
						    
//						     System.err.println("*******************************************");
//						     System.err.println("statisticsList.size():"+statisticsList.size());
//						    int ccc=0;
						    //写入遗漏数据统计信息字体颜色为玫瑰
						    
						   
						    for(int row =0;row<statisticsList.size();row++) {
						    	
						    	CellStyle cellStyle2 = wb.createCellStyle();
							   	cellStyle2.setAlignment(HorizontalAlignment.CENTER);//水平居中
							   	cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
						         
								HSSFFont font2 = wb.createFont();
								font2.setFontName("Arial");
								font2.setFontHeightInPoints((short) 12);
						    	font2.setColor(IndexedColors.VIOLET.getIndex());
							    cellStyle2.setFont(font2);
							    
					    	    HSSFRow hssfRow=sheet.getRow(row+1);
					    	    /*ccc++;
					    	     System.err.println("ccc:"+ccc+"  row:"+row);
					    	    if(row==0) {
					    	    	hssfRow.createCell(11).setCellValue("遗漏期数");  
					    	    	hssfRow.createCell(12).setCellValue("汇总");  
					    	    }else {*/
					    	    	Number daysofdifference = (Number)statisticsList.get(row).get("daysofdifference");
					 			    Number count = (Number)statisticsList.get(row).get("count");
//					 			    System.err.println("row:"+row+"  daysofdifference:"+daysofdifference+" count:"+count);
					 			    hssfRow.createCell(13).setCellValue(daysofdifference.intValue()); 
					 			    hssfRow.createCell(14).setCellValue(count.intValue());  
						    	 	hssfRow.getCell(13).setCellStyle(cellStyle2);
						    	 	hssfRow.getCell(14).setCellStyle(cellStyle2);
//						    	 	 System.err.println("设值完成————————————————————————————————————————————————");
					    	   // }
				 			    
					 	    }
						    // 必须在单元格设值以后进行
						    // 设置为根据内容自动调整列宽 中文不适用
					        for (int k = 0; k < 14; k++) {
					            sheet.autoSizeColumn(k);
					        }   
					        log.info("Sheet"+number+"分类完毕！");
					}
					
					//输出Excel文件  
			        //文件路径（路径+文件名）
					File file = new File(fileURL);
				   	//文件不存在则创建文件，先创建目录
				    if (!file.exists()) {   
				        File dir = new File(file.getParent());
				        dir.mkdirs();
				        file.createNewFile();
				    }
				   	FileOutputStream output=new FileOutputStream(fileURL); 
				   	bos = new BufferedOutputStream(output);
				   	
				   	wb.write(bos); 
				   	
				   	wb.close();
				   	output.flush(); 
				   	
		            log.info("Xls分类完毕！");
		            long endTime=System.currentTimeMillis();
			        log.info("xls分类方法执行时间："+(endTime-startTime)+"ms");
			        log.info("xls分类方法执行时间："+(endTime-startTime)+"ms");
			        log.info("xls分类方法执行时间："+(endTime-startTime)+"ms");
					
				     
				 } catch (Exception e) {
				     e.printStackTrace();
				     // do something
				 } finally {
				       // do something
					 if (sqlSession != null) {
								sqlSession.close();
							}
					 
					 if(bos != null){
			                try {
			                    bos.close();
			                } catch (IOException e) {
			                    e.printStackTrace();
			                }

			            }
				 }
					  
					  
				}
		
		/**
		 **  按照和值大小分类xls
		 **
		 **/
		public static void assortNewXls(String path,String lotteryType){
			
			long startTime=System.currentTimeMillis();
			Date date = new Date();
			SimpleDateFormat dateFormat= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			log.info(dateFormat.format(date));
			//创建HSSFWorkbook对象  
		   	HSSFWorkbook wb = new HSSFWorkbook(); 
		   	SXSSFWorkbook sxssbook=new SXSSFWorkbook();
			
			 String number="23";
			 String fileURL="";
			 SqlSession sqlSession = null;
			 BufferedOutputStream bos = null;
			 try {
					sqlSession = SqlSessionFactoryUtils.openSqlSession();
					ThreeDataMapper threeDataMapper = sqlSession.getMapper(ThreeDataMapper.class);
					ArrayThreeMapper arrayThreeMapper = sqlSession.getMapper(ArrayThreeMapper.class);
					//按和值分类开奖数据
					List<ThreeD> threelist;
					//按和值统计信息数据
					List<Map<Integer,Integer>> statisticsList;
				
					
					for(int i=0;i<=27;i++) {
						    number=Integer.toString(i);
							if(lotteryType.equals("welfare")) {
								threelist=threeDataMapper.queryNewSum(number);
								statisticsList =threeDataMapper.statisticsBySum(number);
								fileURL=path+"ThreeData.xls";
							}else {
								threelist=arrayThreeMapper.queryNewSum(number);
								statisticsList =arrayThreeMapper.statisticsBySum(number);
								fileURL=path+"ArrayThree.xls";
							}
							
							//创建HSSFSheet对象  
							HSSFSheet sheet = wb.createSheet(number); 
							//创建行  
							HSSFRow row2=sheet.createRow(0);      
							//创建单元格并设置单元格内容  
							row2.createCell(0).setCellValue("序号"); 
							row2.createCell(1).setCellValue("日期");  
							row2.createCell(2).setCellValue("期号");      
							row2.createCell(3).setCellValue("开奖号码");  
							row2.createCell(4).setCellValue("百位");  
							row2.createCell(5).setCellValue("十位");  
							row2.createCell(6).setCellValue("个位");      
							row2.createCell(7).setCellValue("形态"); 
							row2.createCell(8).setCellValue("跨度");  
							row2.createCell(9).setCellValue("和值");
							row2.createCell(10).setCellValue("组选");
							row2.createCell(11).setCellValue("除三余数");
							row2.createCell(12).setCellValue("组三重复");   
							row2.createCell(13).setCellValue("遗漏期数");   
							row2.createCell(14).setCellValue("大小");
							row2.createCell(15).setCellValue("奇偶");
							row2.createCell(16).setCellValue("质合");
							row2.createCell(17).setCellValue("升降");
							row2.createCell(18).setCellValue("区间");
							row2.createCell(19).setCellValue("遗漏期数");  
							row2.createCell(20).setCellValue("汇总"); 
							row2.createCell(21).setCellValue("截止"+dateFormat.format(date)+"和值"+number+"数据");  
							 
							
						   	CellStyle cellStyle = wb.createCellStyle();
						   	cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
						   	cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
					         
							HSSFFont font = wb.createFont();
							font.setFontName("Arial");
							font.setFontHeightInPoints((short) 12);
							cellStyle.setFont(font);
							
							
							//表头设置垂直居中、水平居中
							for(int j=0;j<=21;j++) {
								 row2.getCell(j).setCellStyle(cellStyle);
						    }
							 
						    for(int row=0;row<threelist.size();row++) {
						    	 HSSFRow row3=sheet.createRow(row+1);      
						 	    //创建单元格并设置单元格内容  
						    	 
						    	 row3.createCell(0).setCellValue(threelist.get(row).getNumber());
						    	 row3.createCell(1).setCellValue(threelist.get(row).getDate());   
						    	 row3.createCell(2).setCellValue(threelist.get(row).getIssue());   
						    	 row3.createCell(3).setCellValue(threelist.get(row).getResult());  
						    	 row3.createCell(4).setCellValue(threelist.get(row).getHundreds());
						    	 row3.createCell(5).setCellValue(threelist.get(row).getTens());  
						    	 row3.createCell(6).setCellValue(threelist.get(row).getOnes());    
						    	 row3.createCell(7).setCellValue(threelist.get(row).getForm()); 
						    	 row3.createCell(8).setCellValue(threelist.get(row).getSpan());
						    	 row3.createCell(9).setCellValue(threelist.get(row).getSum());
						    	 row3.createCell(10).setCellValue(threelist.get(row).getPacket());  
						    	 row3.createCell(11).setCellValue(threelist.get(row).getRemainder()); 
						    	 row3.createCell(12).setCellValue(threelist.get(row).getArray());
						    	 row3.createCell(13).setCellValue(threelist.get(row).getDaysofdifference());  
						    	 row3.createCell(14).setCellValue(threelist.get(row).getSize()); 
						    	 row3.createCell(15).setCellValue(threelist.get(row).getOddeven()); 
						    	 row3.createCell(16).setCellValue(threelist.get(row).getPrimeOrComposite()); 
						    	 row3.createCell(17).setCellValue(threelist.get(row).getOrder()); 
						    	 row3.createCell(18).setCellValue(threelist.get(row).getInterval());
						 	 
						 	    //设置单元格垂直居中、水平居中
						 	    for(int j=0;j<=18;j++) {
						 		  row3.getCell(j).setCellStyle(cellStyle);
							    }
						 	    
						 	    
						 	    
						 	  
						    }
						    
//						     System.err.println("*******************************************");
//						     System.err.println("statisticsList.size():"+statisticsList.size());
//						    int ccc=0;
						    //写入遗漏数据统计信息字体颜色为玫瑰
						    
						   
						    for(int row =0;row<statisticsList.size();row++) {
						    	
						    	CellStyle cellStyle2 = wb.createCellStyle();
							   	cellStyle2.setAlignment(HorizontalAlignment.CENTER);//水平居中
							   	cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
						         
								HSSFFont font2 = wb.createFont();
								font2.setFontName("Arial");
								font2.setFontHeightInPoints((short) 12);
						    	font2.setColor(IndexedColors.VIOLET.getIndex());
							    cellStyle2.setFont(font2);
							    
					    	    HSSFRow hssfRow=sheet.getRow(row+1);
					    	    /*ccc++;
					    	     System.err.println("ccc:"+ccc+"  row:"+row);
					    	    if(row==0) {
					    	    	hssfRow.createCell(11).setCellValue("遗漏期数");  
					    	    	hssfRow.createCell(12).setCellValue("汇总");  
					    	    }else {*/
					    	    	Number daysofdifference = (Number)statisticsList.get(row).get("daysofdifference");
					 			    Number count = (Number)statisticsList.get(row).get("count");
//					 			    System.err.println("row:"+row+"  daysofdifference:"+daysofdifference+" count:"+count);
					 			    hssfRow.createCell(19).setCellValue(daysofdifference.intValue()); 
					 			    hssfRow.createCell(20).setCellValue(count.intValue());  
						    	 	hssfRow.getCell(19).setCellStyle(cellStyle2);
						    	 	hssfRow.getCell(20).setCellStyle(cellStyle2);
//						    	 	 System.err.println("设值完成————————————————————————————————————————————————");
					    	   // }
				 			    
					 	    }
						    // 必须在单元格设值以后进行
						    // 设置为根据内容自动调整列宽 中文不适用
					        for (int k = 0; k < 14; k++) {
					            sheet.autoSizeColumn(k);
					        }   
					        log.info("Sheet"+number+"分类完毕！");
					}
					
					//输出Excel文件  
			        //文件路径（路径+文件名）
					File file = new File(fileURL);
				   	//文件不存在则创建文件，先创建目录
				    if (!file.exists()) {   
				        File dir = new File(file.getParent());
				        dir.mkdirs();
				        file.createNewFile();
				    }
				   	FileOutputStream output=new FileOutputStream(fileURL); 
				   	bos = new BufferedOutputStream(output);
				   	
				   	wb.write(bos); 
				   	
				   	wb.close();
				   	output.flush(); 
				   	
		            log.info("Xls分类完毕！");
		            long endTime=System.currentTimeMillis();
			        log.info("xls分类方法执行时间："+(endTime-startTime)+"ms");
			        log.info("xls分类方法执行时间："+(endTime-startTime)+"ms");
			        log.info("xls分类方法执行时间："+(endTime-startTime)+"ms");
					
				     
				 } catch (Exception e) {
				     e.printStackTrace();
				     // do something
				 } finally {
				       // do something
					 if (sqlSession != null) {
								sqlSession.close();
							}
					 
					 if(bos != null){
			                try {
			                    bos.close();
			                } catch (IOException e) {
			                    e.printStackTrace();
			                }

			            }
				 }
					  
					  
				}
		
		
		
		/**
		 ** 计算和值遗漏期数
		 **/
		  
		public  static int countDays(String issue1,String issue2 ) {
			
				int form=0;
				SqlSession sqlSession = null;
				
				try {
						
						sqlSession = SqlSessionFactoryUtils.openSqlSession();
						
						//福彩的期号 2021XXX
						if(issue1.length()==7 && issue2.length()==7 ) {
							ThreeDataMapper threeDataMapper = sqlSession.getMapper(ThreeDataMapper.class);
							form=threeDataMapper.queryNo(issue2)-threeDataMapper.queryNo(issue1);
						}else if (issue1.length()==5 &&  issue2.length()==5) {
							//体彩的期号 21XXX
							ArrayThreeMapper arrayThreeMapper = sqlSession.getMapper(ArrayThreeMapper.class);
							form=arrayThreeMapper.queryNo(issue2)-arrayThreeMapper.queryNo(issue1);	
						}
						
				}catch (Exception e) {
				     e.printStackTrace();
				     // do something
				 } finally {
				       // do something
					 if (sqlSession != null) {
								sqlSession.close();
							}
				 }
//				遗漏期数，在序号差之后再减一
				return form-1;
			}
		/**
		 * * 更新所有和值的遗漏期数
		 * 次算法前提是包括此前所有期数的数据，如有数据缺失则会影响数据准确度
		 *  */
		public static void updateSumDays(String lotteryType) {
			try {
				long startTime=System.currentTimeMillis();
				for(int i=0;i<=27;i++) {
					SumDays(String.valueOf(i),lotteryType);
				}
				long endTime=System.currentTimeMillis();
				log.info("更新所有和值的遗漏期数updateSumDays执行时间："+(endTime-startTime)+"ms");
			}catch (Exception e) {
			     e.printStackTrace();
			     // do something
			 }
		}
		/**
		 * * 更新和值sum的遗漏期数
		 *  */
		public static void SumDays(String sum,String lotteryType) {
			long startTime=System.currentTimeMillis();
			int days=0;
			SqlSession sqlSession = null;
			try {
				
				sqlSession = SqlSessionFactoryUtils.openSqlSession();
				
				
				List<ThreeD> sumList;
				if(lotteryType.equals("welfare")) {
					//log.info("888888888888888888888888888888888888888888888888888888888888");
					//log.info("threeDataMapper:"+lotteryType);
					ThreeDataMapper threeDataMapper = sqlSession.getMapper(ThreeDataMapper.class);
					sumList=threeDataMapper.querySum(sum);
					for(int i=0;i<sumList.size();i++) {
						//防止重复计算
						if(sumList.get(i).getDaysofdifference() < 0 ) {
							if(i==0) {
								sumList.get(i).setDaysofdifference(0);
							}else {
								days=countDays(sumList.get(i-1).getIssue(),sumList.get(i).getIssue());
								sumList.get(i).setDaysofdifference(days);
							}
							threeDataMapper.updateDaysofdifference(sumList.get(i).getIssue(),sumList.get(i).getDaysofdifference());
//							log.info("welfarewelfarewelfare88888888888888888888888888888888888888888888888888888888");
//							log.info(sumList.get(i).getIssue()+"    "+sumList.get(i).getDaysofdifference());
						}
					}
				}else {
					//log.info("888888888888888888888888888888888888888888888888888888888888");
					//log.info("arrayThreeMapper:"+lotteryType);
					ArrayThreeMapper arrayThreeMapper = sqlSession.getMapper(ArrayThreeMapper.class);
					sumList=arrayThreeMapper.querySum(sum);	
					for(int i=0;i<sumList.size();i++) {
						//防止重复计算
						if(sumList.get(i).getDaysofdifference() < 0 ) {
							if(i==0) {
								sumList.get(i).setDaysofdifference(0);
							}else {
								days=countDays(sumList.get(i-1).getIssue(),sumList.get(i).getIssue());
								sumList.get(i).setDaysofdifference(days);
							}
							arrayThreeMapper.updateDaysofdifference(sumList.get(i).getIssue(),sumList.get(i).getDaysofdifference());
							//log.info("sportssportssports88888888888888888888888888888888888888888888888888888888");
							//log.info(sumList.get(i).getIssue()+"    "+sumList.get(i).getDaysofdifference());
						}
					}
				}
				sqlSession.commit();//提交数据库操作
				long endTime=System.currentTimeMillis();
				log.info("更新和值"+sum+"的遗漏期数执行时间："+(endTime-startTime)+"ms");
			}catch (Exception e) {
			     e.printStackTrace();
			     // do something
			 } finally {
			       // do something
				 if (sqlSession != null) {
							sqlSession.close();
						}
			 }
				return ;
				
		}
		
		
		
		/**
		 * * 更新和值sum的遗漏期数
		 *  */
		public static void remainderDays(String remainder,String lotteryType) {
			long startTime=System.currentTimeMillis();
			int days=0;
			SqlSession sqlSession = null;
			try {
				
				sqlSession = SqlSessionFactoryUtils.openSqlSession();
				
				
				List<ThreeD> sumList;
				if(lotteryType.equals("welfare")) {
					//log.info("888888888888888888888888888888888888888888888888888888888888");
					//log.info("threeDataMapper:"+lotteryType);
					ThreeDataMapper threeDataMapper = sqlSession.getMapper(ThreeDataMapper.class);
					sumList=threeDataMapper.queryRemainder(remainder);
					for(int i=0;i<sumList.size();i++) {
						//防止重复计算
						if(sumList.get(i).getDaysofdifference() < 0 ) {
							if(i==0) {
								sumList.get(i).setDaysofdifference(0);
							}else {
								days=countDays(sumList.get(i-1).getIssue(),sumList.get(i).getIssue());
								sumList.get(i).setDaysofdifference(days);
							}
							threeDataMapper.updateDaysofdifference(sumList.get(i).getIssue(),sumList.get(i).getDaysofdifference());
//							log.info("welfarewelfarewelfare88888888888888888888888888888888888888888888888888888888");
//							log.info(sumList.get(i).getIssue()+"    "+sumList.get(i).getDaysofdifference());
						}
					}
				}else {
					//log.info("888888888888888888888888888888888888888888888888888888888888");
					//log.info("arrayThreeMapper:"+lotteryType);
					ArrayThreeMapper arrayThreeMapper = sqlSession.getMapper(ArrayThreeMapper.class);
					sumList=arrayThreeMapper.queryRemainder(remainder);	
					for(int i=0;i<sumList.size();i++) {
						//防止重复计算
						if(sumList.get(i).getDaysofdifference() < 0 ) {
							if(i==0) {
								sumList.get(i).setDaysofdifference(0);
							}else {
								days=countDays(sumList.get(i-1).getIssue(),sumList.get(i).getIssue());
								sumList.get(i).setDaysofdifference(days);
							}
							arrayThreeMapper.updateDaysofdifference(sumList.get(i).getIssue(),sumList.get(i).getDaysofdifference());
							//log.info("sportssportssports88888888888888888888888888888888888888888888888888888888");
							//log.info(sumList.get(i).getIssue()+"    "+sumList.get(i).getDaysofdifference());
						}
					}
				}
				sqlSession.commit();//提交数据库操作
				long endTime=System.currentTimeMillis();
				log.info("更新除三余数"+remainder+"的遗漏期数执行时间："+(endTime-startTime)+"ms");
			}catch (Exception e) {
			     e.printStackTrace();
			     // do something
			 } finally {
			       // do something
				 if (sqlSession != null) {
							sqlSession.close();
						}
			 }
				return ;
				
		}
		
	/**
	 * @Description:
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}
